﻿Public Class acceso
    Dim cadenaDeConexion As String = ""
    Dim cad As New CadenaDeConexion
    Dim conexion As Data.SqlClient.SqlConnection
    Dim cmd As Data.SqlClient.SqlCommand
    Dim trans As Data.SqlClient.SqlTransaction

    Public WriteOnly Property _cadenaDeConexion As String
        Set(ByVal value As String)
            cadenaDeConexion = value
        End Set
    End Property
    
    Public Sub New()
        cadenaDeConexion = cad._cadena
        conexion = New Data.SqlClient.SqlConnection()
        cmd = New Data.SqlClient.SqlCommand()

    End Sub
    Private Sub conectar()
        conexion.ConnectionString = Me.cadenaDeConexion
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
    End Sub
    Public Function ejecutar(ByVal sql As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = sql
        tabla.Load(Me.cmd.ExecuteReader)
        conexion.Close()
        Return tabla
    End Function


    Public Sub ejecutarActualizaciones(ByVal sql As String, ByVal comando As SqlClient.SqlCommand)
        cmd = New Data.SqlClient.SqlCommand()
        Me.cmd = comando
        Me.conectar()
        Me.cmd.CommandText = sql
        Me.cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub
    Public Sub ejecutarActualizaciones(ByVal sql As String, ByVal comando As SqlClient.SqlCommand, ByRef tran As SqlClient.SqlTransaction, ByRef con As SqlClient.SqlConnection)
        cmd = New Data.SqlClient.SqlCommand()
        Me.cmd = comando
        Me.conectar()
        Me.cmd.CommandText = sql
        Me.cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub
    Public Function leo_tabla(ByVal tabla As String) As Data.DataTable

        Return ejecutar("SELECT * FROM " + tabla)
    End Function
    Public Sub cargarCombo(ByRef combo As ComboBox, ByVal tabla As String, ByVal Display As String, ByVal Value As String)
        combo.DataSource = leo_tabla(tabla)
        combo.DisplayMember = Display
        combo.ValueMember = Value
    End Sub

    Public Sub generarOrdenesDeCompra(ByVal listaOrdenesCompra As List(Of OrdenCompra))
        Try
            conexion.ConnectionString = Me.cadenaDeConexion
            conexion.Open()
            cmd.Connection = conexion
            cmd.CommandType = CommandType.Text
            trans = conexion.BeginTransaction()
            cmd.Transaction = trans
            Dim nroOrdenCompra As Integer = obtenerUltimoNumero("OrdenCompra", "nroOrdenCompra")
            For i As Integer = 0 To listaOrdenesCompra.Count - 1 Step 1
                Dim sql As String = "Insert into OrdenCompra (nroOrdenCompra, fechaCreacion, fechaNecesidad, nroProveedor, idEstado) values (@nroOrdenCompra, @fechaCreacion, @fechaNecesidad, @nroProveedor, @estado)"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@fechaCreacion", listaOrdenesCompra.Item(i)._fechaCreacion)
                cmd.Parameters.AddWithValue("@fechaNecesidad", listaOrdenesCompra.Item(i)._fecheNecesidad)
                cmd.Parameters.AddWithValue("@nroOrdenCompra", nroOrdenCompra + i + 1)
                cmd.Parameters.AddWithValue("@nroProveedor", listaOrdenesCompra.Item(i)._proveedor._idProveedor)
                cmd.Parameters.AddWithValue("@estado", listaOrdenesCompra.Item(i)._estado._idEstado)
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
                For j As Integer = 0 To listaOrdenesCompra.Item(i)._detalle.Count - 1 Step 1
                    Dim sql2 As String = "Insert into DetalleOrden (nroOrden, codProducto, cantidad, precio) values (@nroOrden, @codProducto, @cantidad, @precio)"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@codProducto", listaOrdenesCompra.Item(i)._detalle.Item(j)._producto._codProducto)
                    cmd.Parameters.AddWithValue("@cantidad", listaOrdenesCompra.Item(i)._detalle.Item(j)._cantidad)
                    cmd.Parameters.AddWithValue("@nroOrden", nroOrdenCompra + i + 1)
                    cmd.Parameters.AddWithValue("@precio", listaOrdenesCompra.Item(i)._detalle.Item(j)._precio)
                    cmd.CommandText = sql2
                    cmd.ExecuteNonQuery()
                Next
            Next
            trans.Commit()
            conexion.Close()
        Catch ex As Exception
            trans.Rollback()
            conexion.Close()

        End Try
    End Sub

    Public Sub generarFactura(ByVal factura As ClaseFactura)
        Try

            conexion.ConnectionString = Me.cadenaDeConexion
            conexion.Open()
            cmd.Connection = conexion
            cmd.CommandType = CommandType.Text
            trans = conexion.BeginTransaction()
            cmd.Transaction = trans


            Dim sql As String
            sql = "INSERT INTO Factuas (idTipoFactura, fecha, formaPago, total, nroCliente ) Values (@idTipoFactura, @fecha, @formaPago, @total, @nroCliente)"
            cmd.Parameters.AddWithValue("@idTipoFactura", factura._tipo)
            cmd.Parameters.AddWithValue("@fecha", factura._fecha)
            cmd.Parameters.AddWithValue("@formaPago", factura._formaPago)
            cmd.Parameters.AddWithValue("@total", factura._total)
            cmd.Parameters.AddWithValue("@nroCliente", factura._nroCliente)
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()


            'Dim tabla As Data.DataTable
            'tabla = acceso.ejecutar("SELECT MAX(nroFactura) FROM Factuas")
            'Dim nroFactura As String = tabla.Rows.Item(0).Item(0).ToString()
            'txt_numeroFactura.Text = nroFactura + 1

            Dim tabla As New Data.DataTable
            Dim tabla2 As New Data.DataTable
            sql = "SELECT MAX(nroFactura) FROM Factuas"
            cmd.CommandText = sql
            tabla.Load(cmd.ExecuteReader)
            Dim nroFactura As String = tabla.Rows.Item(0).Item(0).ToString()




            For i As Integer = 0 To factura._detalleFactura.Count - 1 Step 1
                sql = "Insert into DetalleFactura (nroFactura, codProducto, nombre, precio, idMarca, subtotal, cantidad) values (@nroFactura, @codProducto, @nombre, @precio, @idMarca, @subtotal, @cantidad)"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@nroFactura", nroFactura)
                cmd.Parameters.AddWithValue("@codProducto", factura._detalleFactura(i)._producto._codProducto)
                cmd.Parameters.AddWithValue("@nombre", factura._detalleFactura(i)._producto._nombre)
                cmd.Parameters.AddWithValue("@precio", factura._detalleFactura(i)._producto._precio)
                cmd.Parameters.AddWithValue("@idMarca", factura._detalleFactura(i)._producto._marca._idMarca)
                cmd.Parameters.AddWithValue("@subtotal", factura._detalleFactura(i)._subTotal)
                cmd.Parameters.AddWithValue("@cantidad", factura._detalleFactura(i)._cantidad)
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()

                'ACTUALIZACION DE STOCK
                sql = "Update Producto set stockDisponible = stockDisponible - @cant where codProducto = @codProd"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@cant", factura._detalleFactura(i)._cantidad)
                cmd.Parameters.AddWithValue("@codProd", factura._detalleFactura(i)._producto._codProducto)
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()


            Next
            MessageBox.Show("Se ha grabado con exito la factura", "Aviso", MessageBoxButtons.OK)
            trans.Commit()
            conexion.Close()
        Catch ex As Exception
            trans.Rollback()
            conexion.Close()
            MessageBox.Show(ex.Message)
            MessageBox.Show("Hubo un problema al cargar la factura, se han eliminado los cambios", "Aviso", MessageBoxButtons.OK)
        End Try




    End Sub
    Public Sub actualizarOrdenCompra(ByVal orden As OrdenCompra)
        Try
            conexion.ConnectionString = Me.cadenaDeConexion
            conexion.Open()
            cmd.Connection = conexion
            cmd.CommandType = CommandType.Text
            trans = conexion.BeginTransaction()
            cmd.Transaction = trans
            Dim sql As String = "Update OrdenCompra Set fechaNecesidad = @fechaNecesidad WHERE nroOrdenCompra = @nroOrdenCompra"
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@nroOrdenCompra", orden._nroOrden)
            cmd.Parameters.AddWithValue("@fechaNecesidad", orden._fecheNecesidad)
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            Dim sql3 As String = "Delete from DetalleOrden WHERE nroOrden = @nroOrden"
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@nroOrden", orden._nroOrden)
            cmd.CommandText = sql3
            cmd.ExecuteNonQuery()
            For j As Integer = 0 To orden._detalle.Count - 1 Step 1
                Dim sql2 As String = "Insert into DetalleOrden (nroOrden, codProducto, cantidad, precio) values (@nroOrden, @codProducto, @cantidad, @precio)"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@codProducto", orden._detalle(j)._producto._codProducto)
                cmd.Parameters.AddWithValue("@cantidad", orden._detalle(j)._cantidad)
                cmd.Parameters.AddWithValue("@nroOrden", orden._nroOrden)
                cmd.Parameters.AddWithValue("@precio", orden._detalle(j)._precio)
                cmd.CommandText = sql2
                cmd.ExecuteNonQuery()
            Next
            trans.Commit()
            conexion.Close()
        Catch ex As Exception
            trans.Rollback()
            conexion.Close()

        End Try
    End Sub

    Public Function obtenerUltimoNumero(ByVal tabla As String, ByVal campo As String) As Integer
        Dim numero As Integer = 0
        Dim t As New Data.DataTable
        Dim comando As New SqlClient.SqlCommand
        comando.Connection = conexion
        comando.Transaction = trans
        comando.CommandText = "Select MAX( " & campo & " ) as maximo from " & tabla
        t.Load(comando.ExecuteReader)
        If IsDBNull(t.Rows(0).Item(0)) = False Then
            numero = t.Rows(0)(0)
        End If

        Return numero
    End Function
    Public Function obtenerUltimoNumeroasdasd(ByVal tabla As String, ByVal campo As String) As Data.DataTable
        Dim numero As Integer = 0
        Dim t As New Data.DataTable
        Dim comando As New SqlClient.SqlCommand
        comando.Connection = conexion
        conexion.Open()
        comando.CommandText = "Select MAX( " & campo & " ) as maximo from " & tabla
        t.Load(comando.ExecuteReader)
        Return t
    End Function


End Class
